package dbops

/*
 数据库相关
*/
import (
	"Video-go/api/defs"
	"Video-go/api/utils"
	"database/sql"
	"log"
	"time"
)

// AddUserCredential 添加用户
func AddUserCredential(loginName string, pwd string) error {
	// 预编译
	stmt, err := dbConn.Prepare("INSERT INTO users(login_name,pwd)VALUES (?, ?)")
	if err != nil {
		return err
	}
	_, err = stmt.Exec(loginName, pwd)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return nil
}

// GetUserCredential 获取用户
func GetUserCredential(loginName string) (string, error) {
	stmt, err := dbConn.Prepare("SELECT pwd FROM users WHERE  login_name = ?")
	if err != nil {
		log.Printf("GetUser Error: %s", err)
		return "", err
	}
	var pwd string
	// 扫描到pwd
	err = stmt.QueryRow(loginName).Scan(&pwd)
	if err != nil && err != sql.ErrNoRows {
		return "", err
	}
	if err == sql.ErrNoRows {
		return "", nil
	}
	defer stmt.Close()
	return pwd, nil
}

// DeleteUser 删除用户
func DeleteUser(loginName string, pwd string) error {
	stmt, err := dbConn.Prepare("DELETE FROM users WHERE login_name=? AND pwd=?")
	if err != nil {
		log.Printf("DeleteUser Error: %s", err)
		return err
	}
	_, err = stmt.Exec(loginName, pwd)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return nil
}

/* 视频资源相关 */
// AddNewVideo 添加视频
func AddNewVideo(aid int, name string) (*defs.VideoInfo, error) {
	vid, err := utils.NewUUID() // uuid
	if err != nil {
		return nil, err
	}
	t := time.Now()

	ctime := t.Format("2006-01-02 15:04:05") // 格式化时间
	stmt, err := dbConn.Prepare(`INSERT INTO video_info
			(id,author_id,name,display_ctime)values (?,?,?,?)`)
	if err != nil {
		return nil, err
	}
	_, err = stmt.Exec(vid, aid, name, ctime)
	if err != nil {
		return nil, err
	}
	ret := &defs.VideoInfo{Id: vid, AuthorId: aid, Name: name, DisplayCtime: ctime}
	defer stmt.Close()
	return ret, nil
}

// GetVideo 获取视频
func GetVideo(vid string) (*defs.VideoInfo, error) {

	stmt, err := dbConn.Prepare("select author_id,name,display_ctime FROM video_info where id=?")
	var aid int
	var dct string
	var name string
	err = stmt.QueryRow(vid).Scan(&aid, &name, &dct)
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}
	if err == sql.ErrNoRows {
		return nil, nil
	}
	defer stmt.Close()
	ret := &defs.VideoInfo{Id: vid, AuthorId: aid, Name: name, DisplayCtime: dct}
	return ret, nil
}

// DeleteVideo 删除视频
func DeleteVideo(vid string) error {
	stmt, err := dbConn.Prepare("DELETE FROM video_info where id=?")
	if err != nil {
		return err
	}
	_, err = stmt.Exec(vid)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return nil
}

/* 评论相关 */
// AddComment 添加评论
func AddComment(vid string, aid int, content string) error {
	id, err := utils.NewUUID() //uid
	if err != nil {
		return err
	}
	stmt, err := dbConn.Prepare("INSERT INTO comments(id,video_id,author_id,content) values (?,?,?,?)")
	if err != nil {
		return err
	}
	_, err = stmt.Exec(id, vid, aid, content)
	if err != nil {
		return err
	}
	defer stmt.Close()
	return nil
}

// ListComments 获取评论列表
func ListComments(vid string, from, to int) ([]*defs.Comment, error) {
	// 查询 用户和评论联合查询
	stmt, err := dbConn.Prepare(`SELECT comments.id, users.login_name, comments.content FROM 
		comments INNER JOIN users ON comments.author_id = users.id
		WHERE comments.video_id = ? AND comments.time > FROM_UNIXTIME(?) AND 
		comments.time <= FROM_UNIXTIME(?)`)
	if err != nil {
		return nil, err
	}
	var ret []*defs.Comment
	rows, err := stmt.Query(vid, from, to)
	if err != nil {
		return ret, err
	}
	for rows.Next() {
		var id, name, content string
		if err := rows.Scan(&id, &name, &content); err != nil {
			return ret, err
		}
		tmp := &defs.Comment{Id: id, VideoId: vid, Author: name, Content: content}
		ret = append(ret, tmp)
	}
	defer stmt.Close()
	return ret, nil
}

// GetUser 通过用户名获取用户返回指针
func GetUser(loginName string) (*defs.User, error) {
	stmt, err := dbConn.Prepare("SELECT id, pwd FROM users WHERE login_name = ?")
	if err != nil {
		log.Printf("%s", err)
		return nil, err
	}
	var id int
	var pwd string
	err = stmt.QueryRow(loginName).Scan(&id, &pwd)
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}
	if err == sql.ErrNoRows {
		return nil, nil
	}
	ret := &defs.User{Id: id, LoginName: loginName, Pwd: pwd}
	defer stmt.Close()
	return ret, nil
}

// ListVideoInfo 获取视频列表
func ListVideo(uname string, from, to int) ([]*defs.VideoInfo, error) {
	stmt, err := dbConn.Prepare(`SELECT video_info.id, video_info.author_id, video_info.name, video_info.display_ctime FROM video_info 
		INNER JOIN users ON video_info.author_id = users.id
		WHERE users.login_name = ? AND video_info.create_time > FROM_UNIXTIME(?) AND video_info.create_time <= FROM_UNIXTIME(?) 
		ORDER BY video_info.create_time DESC`)
	var ret []*defs.VideoInfo
	if err != nil {
		return ret, err
	}
	rows, err := stmt.Query(uname, from, to)
	if err != nil {
		log.Printf("%s", err)
		return ret, err
	}
	for rows.Next() {
		var id, name, ctime string
		var aid int
		if err := rows.Scan(&id, &aid, &name, &ctime); err != nil {
			return ret, err
		}
		vi := &defs.VideoInfo{Id: id, AuthorId: aid, Name: name, DisplayCtime: ctime}
		ret = append(ret, vi)
	}
	defer stmt.Close()
	return ret, nil
}
